﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using MySql.Data.MySqlClient;
using System.Threading;

namespace ImportDB
{
    public class MysqlHelper
    {
        string connString = string.Empty;
        MySqlConnection myConnection;

        //try the connection 
        public static MysqlHelper GetMysqlHelper(string argServer,string argDB,string argUid,string argPwd,string argErr)
        {
            string connstr = string.Format("server={0};database={1};uid={2};pwd={3}",argServer,argDB,argUid,argPwd);
            try
            {
                var conn = OpenConnection(connstr);
                conn.Close();
                return new MysqlHelper(connstr);
            }
            catch(Exception ex)
            {
                argErr = ex.Message;
                return null;
            }
        }
        

        public static MysqlHelper GetMysqlHelperWithoutDB(string argServer, string argUid, string argPwd, string argErr)
        {
            string connstr = string.Format("server={0};uid={2};pwd={3}");
            try
            {
                var conn = OpenConnection(connstr);
                conn.Close();
                return new MysqlHelper(connstr);
            }
            catch (Exception ex)
            {
                argErr = ex.Message;
                return null;
            }
        }

        public static MySqlConnection OpenConnection(string argConnectionString)
        {
            MySqlConnection conn = new MySqlConnection(argConnectionString);
            conn.Open();
            return conn;
        }



        public MySqlConnection MyConnection
        {
            get
            {
                if (this.myConnection == null)
                {
                    this.myConnection = OpenConnection(this.connString);
                }
                if (myConnection.State != ConnectionState.Open)
                {
                    myConnection.Open();
                }
                return this.myConnection;
            }
        }


        public MysqlHelper(string argConnectionString)
        {
            this.connString = argConnectionString;
        }

        public void CreateDB(string argDbName)
        {
            string sql = "create database " + argDbName;
            ExecSql(sql);
        }

        private void ExecSql(string argSql)
        {
            MySqlCommand cmd = this.MyConnection.CreateCommand();
            cmd.CommandText = argSql;
            cmd.ExecuteNonQuery();
            this.MyConnection.Close();
        }

        public DataTable GetDataTable(string argSql)
        {
            
            DataTable dt = new DataTable();
            using (myConnection)
            {
                MySqlDataAdapter da = new MySqlDataAdapter(argSql,this.MyConnection);
                da.Fill(dt);
                myConnection.Close();
            }
            return dt;
        }

        public void CreateTableByDataTable(DataTable argDataTable, string argTableName,Dictionary<string,int> colWidths)
        {
            MySqlCommand cmd = this.MyConnection.CreateCommand();
            StringBuilder sbcolumns = new StringBuilder();
            sbcolumns.Append("create table " + argTableName);
            sbcolumns.Append(" (`" + argTableName + "_id` int primary key AUTO_INCREMENT,");
            foreach (DataColumn col in argDataTable.Columns)
            {
                sbcolumns.Append("`" + col.ColumnName.Trim() + "`");
                sbcolumns.Append( string.Format(" varchar({0}) " , colWidths[col.ColumnName] ));
                if (col.ColumnName != argDataTable.Columns[argDataTable.Columns.Count - 1].ColumnName)
                {
                    sbcolumns.Append(",");
                }
            }
            sbcolumns.Append(")");
            cmd.CommandText = sbcolumns.ToString();
            cmd.ExecuteNonQuery();
        }

        public bool IsTableExisted(string argTableName)
        {
            string sql = "show tables like '" + argTableName + "'";
            var dt = GetDataTable(sql);
            return dt.Rows.Count > 0;

        }

        public void ImportData(DataTable argDataTable, string argTableName, Dictionary<string, int> colWidths,System.ComponentModel.BackgroundWorker argWorker)
        {
            if (!IsTableExisted(argTableName))
            {
                CreateTableByDataTable(argDataTable, argTableName, colWidths);
            }
            InsertNewData(argTableName, argDataTable,argWorker);
        }

        private  void InsertNewData(string argTableName, DataTable argDataTable,System.ComponentModel.BackgroundWorker argWorker)
        {
            const int C_BatchSize=100;
            

            DataTable dtTemp = argDataTable.Clone();
            dtTemp.TableName = argDataTable.TableName;
            int currentRowIndex = 0;
            int totalRows = argDataTable.Rows.Count;

            MySqlDataAdapter da = GetDataAdapter(argTableName);
            

            
                while (currentRowIndex < totalRows)
                {
                    for (int i = 0; i < C_BatchSize && currentRowIndex + i < totalRows; i++)
                    {
                        argDataTable.Rows[currentRowIndex + i].SetAdded();
                        dtTemp.ImportRow(argDataTable.Rows[currentRowIndex + i]);
                    }

                    var trans = this.MyConnection.BeginTransaction();
                    
                    da.InsertCommand.Transaction = trans;

                    try
                    {
                        da.Update(dtTemp);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                    }

                    dtTemp.Clear();
                    currentRowIndex += C_BatchSize;
                    int percent = currentRowIndex * 100 / totalRows;
                    if (percent > 100) percent = 100;
                    argWorker.ReportProgress(percent);
                }
            
        }

        private MySqlDataAdapter GetDataAdapter(string argTableName)
        {
            MySqlDataAdapter da = new MySqlDataAdapter("select * from " + argTableName + " limit 1,1 ", this.MyConnection);
            da.UpdateBatchSize = 100;
            MySqlCommandBuilder builder = new MySqlCommandBuilder(da);
            builder.QuotePrefix = "`";
            builder.QuoteSuffix = "`";
            da.InsertCommand = builder.GetInsertCommand();
            return da;
        }


    }
}
